//Charlie risky user query v6- slight optimizations
National_AADRiskyUsers
| where TimeGenerated > ago(7d) //<-- Alter the number/letter combination here for different time periods - (e.g, 24h, 1m, 7d, 1w etc.)
| where RiskState == "atRisk" or RiskDetail == "adminConfirmedUserCompromised"
| join kind= leftouter (National_IdentityInfo
    | where TimeGenerated >= ago (30d)
    | where OnPremisesDistinguishedName contains_cs "HDD/PATH" //<-- Desired OU path goes here
    | project AccountDisplayName, AccountObjectId
    )
    on $left.Id == $right.AccountObjectId
| summarize any(UserPrincipalName, RiskLevel, RiskState, TimeGenerated, AccountDisplayName) by AccountObjectId //deduplication to alleviate CPU load
| project
    TimeGenerated=any_TimeGenerated,
    UserPrincipalName=any_UserPrincipalName,
    RiskLevel=any_RiskLevel,
    RiskState=any_RiskState,
    AccountDisplayName=any_AccountDisplayName,
    AccountObjectId
| where coalesce(AccountDisplayName, "") != "" //Remediate null values
| join kind= leftouter National_AADUserRiskEvents on UserPrincipalName
| extend loc = parse_json(Location)
| extend
    city = tostring(loc.city),
    state = tostring(loc.state),
    country = tostring(loc.countryOrRegion)
| extend Device = extract(@'\"Key\":\"userAgent\",\"Value\":\"([^\"]*)\"', 1, tostring(AdditionalInfo))
| extend Location = strcat(city, ", ", state, ", ", country)
| summarize any(TimeGenerated, UserPrincipalName, ActivityDateTime, Device, Location, IpAddress, RiskEventType, RiskLevel, RiskState) by AccountObjectId
| project
    TimeGenerated=any_TimeGenerated,
    UserPrincipalName=any_UserPrincipalName,
    ActivityDateTime=any_ActivityDateTime,
    Device=any_Device,
    Location=any_Location,
    IpAddress=any_IpAddress,
    RiskEventType=any_RiskEventType,
    RiskLevel=any_RiskLevel,
    RiskState=any_RiskState